package proyecto.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import javax.sql.DataSource;

import proyecto.exception.DAOExcepcion;
import proyecto.modelo.TblDatosSolicitudCv;


public class TblDatosSolicitudCvDAOImpl extends BaseDAO implements TblDatosSolicitudCvDAO{

	
private DataSource dataSource;
	
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}	
	
	public Collection<TblDatosSolicitudCv> consulta_listado_filtros(int indept, int intprov, int intdist, String vcgradoIns, String vcCarrera ) throws DAOExcepcion {
		Collection<TblDatosSolicitudCv> c = new ArrayList<TblDatosSolicitudCv>();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = dataSource.getConnection();
			String query = "select in_codigo,vc_nombres,vc_apePat,vc_apeMat,ch_tipDoc," +
							"vc_nroDoc,vc_email,vc_telFij,vc_telCel,da_fecNac," +
							"bo_tieneHijos,bo_sexo,by_estCivil,vc_direccion,in_distrito,in_departamento," +
							"vc_gradoIns,vc_carrera,vc_centroEstudios,vc_duracionCarrera,vc_perfilWebCv,vc_comentarios " + 
							" from tbl_datos_solicitud_cv where in_distrito like ? and vc_generoPelicula like ?";
			stmt = con.prepareStatement(query);
			stmt.setString(1, "%" + indept + "%");
			stmt.setString(2, "%" + intprov + "%");
			stmt.setString(3, "%" + intdist + "%");
			stmt.setString(4, "%" + vcgradoIns + "%");
			stmt.setString(5, "%" + vcCarrera + "%");
			rs = stmt.executeQuery();
			while (rs.next()) {
				TblDatosSolicitudCv vo = new TblDatosSolicitudCv();
				vo.setInCodigo1(rs.getInt("in_codigo")); 
				vo.setVcNombres(rs.getString("vc_nombres")); 
				vo.setVc_apePat(rs.getString("vc_apePat"));
				vo.setVc_apeMat(rs.getString("vc_apeMat")); 
				vo.setCh_tipDoc(rs.getString("ch_tipDoc"));
				vo.setVc_nroDoc(rs.getString("vc_nroDoc"));
				vo.setVcEmail(rs.getString("vc_email"));
				vo.setVc_telFij(rs.getString("vc_telFij"));
				vo.setVc_telCel(rs.getString("vc_telCel"));
				vo.setDa_fecNac(rs.getDate("da_fecNac"));
				vo.setBo_tieneHijos1(rs.getByte("bo_tieneHijos"));
				vo.setBoSexo1(rs.getByte("bo_sexo"));
				vo.setBy_estCivil(rs.getString("by_estCivil"));
				vo.setVcDireccion(rs.getString("vc_direccion"));
				vo.setInDistrito1(rs.getInt("in_distrito"));
				//vo.setInProvincia()
				vo.setInDepartamento1(rs.getInt("in_departamento"));					
				vo.setVc_gradoIns(rs.getString("vc_gradoIns"));
				vo.setVcCarrera(rs.getString("vc_carrera"));
				vo.setVc_centroEstudios(rs.getString("vc_centroEstudios"));
				vo.setVc_duracionCarrera(rs.getString("vc_duracionCarrera"));
				vo.setVc_perfilWebCv(rs.getString("vc_perfilWebCv"));
				vo.setVcComentarios(rs.getString("vc_comentarios"));	
				c.add(vo);
				System.out.println("Registro: " + vo.getInCodigo() + 
				 		   "," + vo.getVcNombres() + 
				 		   "," + vo.getVcDireccion() + 
				 		   "," + vo.getVc_telFij());	
			}

		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return c;
	}
	
	public TblDatosSolicitudCv detalle_TblDatosSolicitudCv(int in_codigo) throws DAOExcepcion {
		TblDatosSolicitudCv vo = new TblDatosSolicitudCv();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String query =  "select in_codigo,vc_nombres,vc_apePat,vc_apeMat,ch_tipDoc," +
							"vc_nroDoc,vc_email,vc_telFij,vc_telCel,da_fecNac," +
							"bo_tieneHijos,bo_sexo,by_estCivil,vc_direccion,in_distrito,in_departamento," +
							"vc_gradoIns,vc_carrera,vc_centroEstudios,vc_duracionCarrera,vc_perfilWebCv,vc_comentarios " +
							"from tbl_datos_solicitud_cv where in_codigo=? ";
			con = dataSource.getConnection();
			stmt = con.prepareStatement(query);
			stmt.setInt(1, in_codigo);
			rs = stmt.executeQuery();
			if (rs.next()) {
				vo.setInCodigo1(rs.getInt("in_codigo")); 
				vo.setVcNombres(rs.getString("vc_nombres")); 
				vo.setVc_apePat(rs.getString("vc_apePat"));
				vo.setVc_apeMat(rs.getString("vc_apeMat")); 
				vo.setCh_tipDoc(rs.getString("ch_tipDoc"));
				vo.setVc_nroDoc(rs.getString("vc_nroDoc"));
				vo.setVcEmail(rs.getString("vc_email"));
				vo.setVc_telFij(rs.getString("vc_telFij"));
				vo.setVc_telCel(rs.getString("vc_telCel"));
				vo.setDa_fecNac(rs.getDate("da_fecNac"));
				vo.setBo_tieneHijos1(rs.getByte("bo_tieneHijos"));
				vo.setBoSexo1(rs.getByte("bo_sexo"));
				vo.setBy_estCivil(rs.getString("by_estCivil"));
				vo.setVcDireccion(rs.getString("vc_direccion"));
				vo.setInDistrito1(rs.getInt("in_distrito"));
				vo.setInDepartamento1(rs.getInt("in_departamento"));					
				vo.setVc_gradoIns(rs.getString("vc_gradoIns"));
				vo.setVcCarrera(rs.getString("vc_carrera"));
				vo.setVc_centroEstudios(rs.getString("vc_centroEstudios"));
				vo.setVc_duracionCarrera(rs.getString("vc_duracionCarrera"));
				vo.setVc_perfilWebCv(rs.getString("vc_perfilWebCv"));
				vo.setVcComentarios(rs.getString("vc_comentarios"));
				System.out.println("Registro: " + vo.getInCodigo() + 
				 		   "," + vo.getVcNombres() + 
				 		   "," + vo.getVcDireccion() + 
				 		   "," + vo.getVc_telFij());				
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return vo;
	}	
	
	public TblDatosSolicitudCv insertar (TblDatosSolicitudCv vo) throws DAOExcepcion {
		String query = "insert into tbl_datos_solicitud_cv (vc_nombres,vc_apePat,vc_apeMat,ch_tipDoc," +
				       "vc_nroDoc,vc_email,vc_telFij,vc_telCel," +
				       "bo_tieneHijos,bo_sexo,by_estCivil,vc_direccion,in_distrito,in_provincia,in_departamento," +
				       "vc_gradoIns,vc_carrera,vc_centroEstudios,vc_duracionCarrera,vc_perfilWebCv,vc_comentarios) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = dataSource.getConnection();
			stmt = con.prepareStatement(query);
			stmt.setString(1, vo.getVcNombres());
			stmt.setString(2, vo.getVc_apePat());
			stmt.setString(3, vo.getVc_apeMat());
			stmt.setString(4, vo.getCh_tipDoc());
			stmt.setString(5, vo.getVc_nroDoc());
			stmt.setString(6, vo.getVcEmail());
			stmt.setString(7, vo.getVc_telFij());
			stmt.setString(8, vo.getVc_telCel());
			//stmt.setString(9,vo.getDa_fecNac().toString());
			stmt.setByte(9, vo.getBo_tieneHijos());
			stmt.setByte(10, vo.getBoSexo());
			stmt.setString(11, vo.getBy_estCivil());
			stmt.setString(12, vo.getVcDireccion());
			stmt.setInt(13, vo.getInDistrito());
			stmt.setInt(14, vo.getInProvincia());
			stmt.setInt(15, vo.getInDepartamento());
			stmt.setString(16, vo.getVc_gradoIns());
			stmt.setString(17, vo.getVcCarrera());
			stmt.setString(18, vo.getVc_centroEstudios());
			stmt.setString(19, vo.getVc_duracionCarrera());
			stmt.setString(20, vo.getVc_perfilWebCv());	
			stmt.setString(21, vo.getVcComentarios());	
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo insertar");
			} // Obtener el ultimo id 
			query = "SELECT LAST_INSERT_ID()";
			stmt = con.prepareStatement(query);
			rs = stmt.executeQuery();
			if (rs.next()) {
				vo.setInCodigo1(rs.getInt(1));
			}

		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return vo;
	}
	
	public TblDatosSolicitudCv actualizar(TblDatosSolicitudCv vo) throws DAOExcepcion {
		
		String query =  "update tbl_datos_solicitud_cv set vc_nombres=?,vc_apePat=?,vc_apeMat=?," +
						"ch_tipDoc=?,vc_nroDoc=?,vc_email=?,vc_telFij=?," +
						"vc_telCel=?,da_fecNac=?,bo_tieneHijos=?,bo_sexo=?," +
						"by_estCivil=?,vc_direccion=?,in_distrito=?,in_departamento=?,vc_gradoIns=?," +
						"vc_carrera=?,vc_centroEstudios=?,vc_duracionCarrera=?,vc_perfilWebCv=?,vc_comentarios " +
						"where in_codigo=?";
		
		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = dataSource.getConnection();
			stmt = con.prepareStatement(query);
			
			stmt.setString(1, vo.getVcNombres());
			stmt.setString(2, vo.getVc_apePat());
			stmt.setString(3, vo.getVc_apeMat());
			stmt.setString(4, vo.getCh_tipDoc());
			stmt.setString(5, vo.getVc_nroDoc());
			stmt.setString(6, vo.getVcEmail());
			stmt.setString(7, vo.getVc_telFij());
			stmt.setString(8, vo.getVc_telCel());
			stmt.setString(9,  vo.getDa_fecNac().toString());
			stmt.setByte(10, vo.getBo_tieneHijos());
			stmt.setByte(11, vo.getBoSexo());
			stmt.setString(12, vo.getBy_estCivil());
			stmt.setString(13, vo.getVcDireccion());
			stmt.setInt(14, vo.getInDistrito());
			stmt.setInt(15, vo.getInDepartamento());
			stmt.setString(16, vo.getVc_gradoIns());
			stmt.setString(17, vo.getVcCarrera());
			stmt.setString(18, vo.getVc_centroEstudios());
			stmt.setString(19, vo.getVc_duracionCarrera());
			stmt.setString(20, vo.getVc_perfilWebCv());	
			stmt.setString(21, vo.getVcComentarios());				
			stmt.setInt(22, vo.getInCodigo());
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo actualizar");
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return vo;
	}

	@Override
	public Collection<TblDatosSolicitudCv> consulta_listado() {
		// TODO Auto-generated method stub
		return null;
	}



	
	
}
